﻿using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WFCSharp_I
{
    public partial class DemoADO : Form
    {
        private SqlConnection sqlConnection;
        public DemoADO()
        {
            InitializeComponent();
        }

        private void sqlVersionButton_Click(object sender, EventArgs e)
        {
            try
            {
                sqlConnection.Open();
                SqlCommand sqlCommand = new SqlCommand("SELECT * FROM Categories", sqlConnection);
                SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();

                label1.Text = "Field count = " + sqlDataReader.FieldCount.ToString();
                if (sqlDataReader.HasRows == false)
                    MessageBox.Show("No have row");
                else
                {
                    comboBox1.Items.Clear();// breakpoint
                    while (sqlDataReader.Read())
                    {
                        //comboBox1.Items.Add(sqlDataReader.GetString(1));
                        Category c = new Category(
                            sqlDataReader.GetInt32(0),
                            sqlDataReader.GetString(1),
                            sqlDataReader.GetString(2));

                        comboBox1.Items.Add(c);
                    }
                    comboBox1.SelectedIndex = 0;
                }
                
                label2.Text = string.Format("Database : {0}, Version : {1}", sqlConnection.Database, sqlConnection.ServerVersion);
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                sqlConnection.Close();
            }
        }

        private void nonQueryButton_Click(object sender, EventArgs e)
        {
            try
            {
                sqlConnection.Open();
                SqlCommand sqlCommand = new SqlCommand(
                    "UPDATE Categories SET CategoryName = CategoryName + '_T' WHERE CategoryID = @CategoryID", sqlConnection);

                int categoryId = ((Category) comboBox1.SelectedItem).CategoryId;
                sqlCommand.Parameters.AddWithValue("@CategoryID", categoryId);

                int result = sqlCommand.ExecuteNonQuery();

                if (result > 0)
                    MessageBox.Show("Updated successfully");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                sqlConnection.Close();
            }

        }

        private void dataAdapterButton_Click(object sender, EventArgs e)
        {
            try
            {
                sqlConnection.Open();
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(
                    "SELECT * FROM Categories", sqlConnection);

                DataSet dataSet = new DataSet();
                // Lấy dữ liệu từ Database -> DataSet
                sqlDataAdapter.Fill(dataSet, "Categories");

                DataTable dataTable = dataSet.Tables["Categories"];

                comboBox1.DisplayMember = "CategoryName";
                comboBox1.ValueMember = "CategoryId";
                comboBox1.DataSource = dataTable;

                //dataGridView1.DataMember = "Categories";
                dataGridView1.DataSource = dataTable;
                //dataTable.DefaultView.RowFilter = "";
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                sqlConnection.Close();
            }

        }

        private void makeDataTableButton_Click(object sender, EventArgs e)
        {
            DataTable dataTable = new DataTable("Categories");

            DataColumn dataColumn = new DataColumn();

            dataColumn.ColumnName = "CategoryID";
            dataColumn.DataType = typeof(int);

            dataTable.Columns.Add(dataColumn);
            dataTable.PrimaryKey = new DataColumn[] { dataColumn };

            dataColumn = new DataColumn();

            dataColumn.ColumnName = "CategoryName";
            dataColumn.DataType = typeof(string);
            dataColumn.MaxLength = 15;

            dataTable.Columns.Add(dataColumn);
            dataTable.Columns.Add("Description", typeof(string));

            DataRow newRow = dataTable.NewRow();

            newRow["CategoryID"] = 1;
            newRow["CategoryName"] = "Beverages";
            newRow["Description"] = "Soft drinks, coffees, teas, beers, and ales";

            dataTable.Rows.Add(newRow);

            newRow = dataTable.NewRow();

            newRow["CategoryID"] = 2;
            newRow["CategoryName"] = "Condiments";
            newRow["Description"] = "Sweet and savory sauces, relishes, spreads, and seasonings";

            dataTable.Rows.Add(newRow);
            newRow = dataTable.NewRow();

            newRow["CategoryID"] = 3;
            newRow["CategoryName"] = "Confections";
            newRow["Description"] = "Desserts, candies, and sweet breads";

            dataTable.Rows.Add(newRow);
            dataTable.DefaultView.Sort = "CategoryID DESC,CategoryName ASC";

            DataView dataView = new DataView(dataTable);
            dataView.RowFilter = "CategoryID > 1 AND CategoryId < 3";

            dataGridView1.AutoGenerateColumns = true;
            dataGridView1.DataSource = dataTable.DefaultView;
            dataGridView2.DataSource = dataView;
            //dataGridView1.SelectedRows[0].Cells[0]; //-> CategoryID
            MessageBox.Show(dataGridView1.Rows[0].Cells[0].Value.ToString());
        }

        private void ADO_Load(object sender, EventArgs e)
        {
            sqlConnection = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=true;");
        }
    }
}